package com.hongdee.crm.contact.mapper;

import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * @author zhangkai
 * @desc 一句话描述
 **/
public class ConcatSqlProvider {
    /**
     * 拼接维系记录查询
     *
     * @param params
     * @return
     */
    public String query(Map params) throws Exception {
        String sql = new SQL() {{
            SELECT(" distinct c.company_id,o.o_name as company_name ,c.vin,c.plate_num,c.sa_id,u.u_name as sa_name");
            SELECT(" title,parent_id,type_id,content,c.contact_name,c.contact_phone,record_path");
            SELECT(" result_A, result_B,result_C,result_D,task_pcode,task_code ,c.create_time,temp.brand,temp.series,temp.owner_name,temp.owner_tel");
            SELECT(" o.company_ip_address");
            SELECT(" case when length(o.company_ip_address)>=7  and length(c.record_path)>8  then concat('http://',o.company_ip_address,'/',SUBSTR(c.record_path,8)) else '' end  as recordUrl  ");
            SELECT(" c.next_date,c.next_content");
            FROM(" t_crm_car_contact c");
            LEFT_OUTER_JOIN(" t_crm_car_contact_result r on c.id =  r.c_id ");
            LEFT_OUTER_JOIN(" t_bc_user u on u.id = c.sa_id ");
            LEFT_OUTER_JOIN(" t_bc_organ o on o.id = c.store_id ");
            LEFT_OUTER_JOIN(" t_crm_car  temp on temp.company_id = c.company_id and c.vin = temp.vin  ");

            Map queryData = (Map) params.get("queryData");
            // 公司ID
            Object reqCompanyId = queryData.get("companyId");
            if (!StringUtils.isEmpty(reqCompanyId)) {
                WHERE(" c.company_id = '" + reqCompanyId + "' ");
            } else {
                String companyId = TokenUtil.getCompanyId(String.valueOf(params.get("uid")));
                WHERE(" c.company_id = '" + companyId + "' ");
            }
            // 日期转换

            if (null != queryData.get("date")) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Map timeMap = (Map) queryData.get("date");
                if (!StringUtils.isEmpty(timeMap.get("startTime"))) {
                    String startTime = String.valueOf(timeMap.get("startTime"));
                    startTime = startTime.substring(0, 10);
                    Long startTimeLong = sdf.parse(startTime).getTime();
                    WHERE(" c.create_time >  " + startTimeLong);
                }

                if (!StringUtils.isEmpty(timeMap.get("stopTime"))) {
                    String stopTime = String.valueOf(timeMap.get("stopTime"));
                    stopTime = stopTime.substring(0, 10);
                    Long stopTimeLong = sdf.parse(stopTime).getTime();
                    WHERE(" c.create_time >  " + stopTimeLong);
                }
            }

            //车主姓名
            if (!StringUtils.isEmpty(queryData.get("name"))) {
                WHERE(" temp.owner_name like '%" + queryData.get("name").toString() + "%'");
            }
            //
            if (!StringUtils.isEmpty(queryData.get("vin"))) {
                WHERE(" temp.vin like '%" + queryData.get("vin").toString() + "%'");
            }
            if (!StringUtils.isEmpty(queryData.get("plateNum"))) {
                WHERE(" temp.plate_num like '%" + queryData.get("plateNum").toString() + "%'");
            }

            //选择人
            //预约类型 0未预约，1已预约
            if (!StringUtils.isEmpty(queryData.get("newRemind"))) {
                WHERE("c.new_remind = '" + queryData.get("newRemind").toString() + "' ");
            }
            // 维系类别
            if (!StringUtils.isEmpty(queryData.get("parentId"))) {
                WHERE("c.parent_id = '" + queryData.get("parentId").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("nextDate"))) {
                WHERE("c.next_date = '" + queryData.get("nextDate").toString() + "' ");
            }

            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultA"))) {
                WHERE("r.result_a = '" + queryData.get("resultA").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultB"))) {
                WHERE("r.result_b = '" + queryData.get("resultB").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultC"))) {
                WHERE("r.result_c = '" + queryData.get("resultC").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultD"))) {
                WHERE("r.result_d = '" + queryData.get("resultD").toString() + "' ");
            }

            if (!StringUtils.isEmpty(queryData.get("saId"))) {
                WHERE("c.sa_id = '" + queryData.get("saId").toString() + "'");
            }else { // 如果为空默认查自己的
                WHERE("c.sa_id = '" + params.get("uid").toString() + "'");
            }
            GROUP_BY(" c.company_id, c.vin,c.plate_num,c.sa_id,u.u_name, temp.brand,temp.series,temp.owner_name,temp.owner_tel ");
            ORDER_BY(" c.create_time desc");
        }}.toString();
        return sql;
    }

    /***
     * 导出数据
     * @param params
     * @return
     * @throws Exception
     */
    public String exportQuery(Map params) throws Exception {
        return new SQL() {{
            SELECT("c.company_id,c.vin,c.plate_num,c.sa_id,u.u_name as sa_name,temp.brand,temp.series,temp.owner_name,temp.owner_tel ");
            SELECT(" group_concat(concat(if(c.create_time is null ,'',from_unixtime(c.create_time/1000,'%Y-%m-%d %T')),' 类别:',c.title,' 内容:',c.content,'',if(result_a <>'',concat(' 结果:',result_a),'' ),if(result_b <>'' ,concat('-',result_b),''),if(result_c <>'',concat('-',result_c) ,''),if(result_d<>'',concat('-',result_d),''),if( c.next_date <> '',concat('下次跟进:',c.next_date, if(c.next_content <> '',c.next_content,'')),''))order by c.create_time asc SEPARATOR ';\\n') as content");
            FROM(" t_crm_car_contact c");
            LEFT_OUTER_JOIN(" t_crm_car_contact_result r on c.id =  r.c_id ");
            LEFT_OUTER_JOIN(" t_bc_user u on u.id = c.sa_id ");
            LEFT_OUTER_JOIN(" t_crm_car  temp on temp.company_id = c.company_id and c.vin = temp.vin  ");

            Map queryData = (Map) params.get("queryData");
            // 公司ID
            Object reqCompanyId = queryData.get("companyId");
            if (!StringUtils.isEmpty(reqCompanyId)) {
                WHERE(" c.company_id = '" + reqCompanyId + "' ");
            } else {
                String companyId = TokenUtil.getCompanyId(String.valueOf(params.get("uid")));
                WHERE(" c.company_id = '" + companyId + "' ");
            }
            // 日期转换

            if (null != queryData.get("date")) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Map timeMap = (Map) queryData.get("date");
                if (!StringUtils.isEmpty(timeMap.get("startTime"))) {
                    String startTime = String.valueOf(timeMap.get("startTime"));
                    startTime = startTime.substring(0, 10);
                    Long startTimeLong = sdf.parse(startTime).getTime();
                    WHERE(" c.create_time >  " + startTimeLong);
                }

                if (!StringUtils.isEmpty(timeMap.get("stopTime"))) {
                    String stopTime = String.valueOf(timeMap.get("stopTime"));
                    stopTime = stopTime.substring(0, 10);
                    Long stopTimeLong = sdf.parse(stopTime).getTime();
                    WHERE(" c.create_time >  " + stopTimeLong);
                }
            }

            //车主姓名
            if (!StringUtils.isEmpty(queryData.get("name"))) {
                WHERE(" temp.owner_name like '%" + queryData.get("name").toString() + "%'");
            }
            //
            if (!StringUtils.isEmpty(queryData.get("vin"))) {
                WHERE(" temp.vin like '%" + queryData.get("vin").toString() + "%'");
            }
            if (!StringUtils.isEmpty(queryData.get("plateNum"))) {
                WHERE(" temp.plate_num like '%" + queryData.get("plateNum").toString() + "%'");
            }

            //选择人
            //预约类型 0未预约，1已预约
            if (!StringUtils.isEmpty(queryData.get("newRemind"))) {
                WHERE("c.new_remind = '" + queryData.get("newRemind").toString() + "' ");
            }
            // 维系类别
            if (!StringUtils.isEmpty(queryData.get("parentId"))) {
                WHERE("c.parent_id = '" + queryData.get("parentId").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("nextDate"))) {
                WHERE("c.next_date = '" + queryData.get("nextDate").toString() + "' ");
            }

            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultA"))) {
                WHERE("r.result_a = '" + queryData.get("resultA").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultB"))) {
                WHERE("r.result_b = '" + queryData.get("resultB").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultC"))) {
                WHERE("r.result_c = '" + queryData.get("resultC").toString() + "' ");
            }
            // 跟进日期
            if (!StringUtils.isEmpty(queryData.get("resultD"))) {
                WHERE("r.result_d = '" + queryData.get("resultD").toString() + "' ");
            }

            if (!StringUtils.isEmpty(queryData.get("saId"))) {
                WHERE("c.sa_id = '" + queryData.get("saId").toString() + "'");
            }
            GROUP_BY(" c.company_id, c.vin,c.plate_num,c.sa_id,u.u_name, temp.brand,temp.series,temp.owner_name,temp.owner_tel ");
            ORDER_BY(" c.create_time desc ");
        }}.toString();
    }
}
